<?php
if (!defined('APP_ID')) { die('No direct script access allowed'); }

class Category {
	/**
	 * Return category info
	 */
	public static function getInfo($id, $fields = '*') {
		return Query::factory('select')
			->table('tbl_categories')
			->fields($fields)
			->where("id=%d", $id)
			->where("status_id IN (1,2)")
			->limit(1)
			->execute()
			->row();
	}

	/**
	 * Return category data
	 */
	public static function getData($id) {
		return new Data('category.xml', 'tbl_categories_fields', 'category_id', $id);
	}

	/**
	 * Return list of categories
	 */
	public static function getList($parent_id = 0, $options = array()) {
		$type_id = 1;
		if (isset($options['type_id'])) {
			$type_id = (int)$options['type_id'];
		}
		
		$status_id = 1;
		if (isset($options['status_id'])) {
			if (is_array($options['status_id'])) {
				$status_id = array();
				foreach ($options['status_id'] as $item) {
					$status_id[] = (int)$item;
				}
			} else {
				$status_id = (int)$options['status_id'];
			}
		}
		
		$query = Query::factory('select')
			->table('tbl_categories')
			->where("(parent_id=%d)", $parent_id);

		if ($type_id > 0) {
			$query->where("(type_id=%d)", $type_id);
		}
		
		if (is_array($status_id)) {
			$status_str = '';
			foreach ($status_id as $item) {
				if (!empty($status_str)) {
					$status_str .= ',';
				}
				$status_str .= (int)$item;
			}
			$query->where("(status_id IN (%s))", $status_str);
		} else if ($type_id > 0) {
			$query->where("(status_id=%d)", $type_id);
		}

		$query->orderBy('sequence', Query::ORDER_ASC)
			->orderBy('id', Query::ORDER_ASC);
		
		return $query->execute()->rows();
	}

	/**
	 * Draw categories tree (select)
	 */
	public static function drawTreeSelect($type_id, $selected_cat_id = 0, $except_cat_id = -1) {
		$categories = self::getFullList($type_id);
		
		foreach ($categories as $category) {
			if (($type_id > 0) &&
				($category['type_id'] > 0) &&
				($category['type_id'] != $type_id)) { continue; }
			if (intval($except_cat_id) == intval($category['id'])) { continue; }

			$selected = '';
			if (intval($selected_cat_id) == intval($category['id'])) {
				$selected = ' selected';
			}

			printf('<option value="%d"%s>%s%s</option>',
				$category['id'],
				$selected,
				str_repeat('- ', $category['level']),
				$category['name']
			);
		}
	}
	
	/**
	 * Return category id by alias
	 */
	public static function getIdByAlias($alias, $exclude_id = 0) {
		$result = 0;
		if (is_int($alias) || ctype_digit($alias)) {
			$result = intval($alias);
		} else {
			$query = Query::factory('select')
				->table('tbl_categories')
				->fields('id')
				->where("status_id IN (1,2)")
				->where("alias='%s'", Database::escape($alias))
				->limit(1);

			if ($exclude_id > 0) {
				$query->where("id<>%d", $exclude_id);
			}

			$category = $query->execute()->row();
			if ($category) {
				$result = intval($category['id']);
			}
		}

		return $result;
	}

	/**
	 * Return category alias by id
	 */
	public static function getAliasById($id) {
		$result = $id;

		$query = Query::factory('select')
			->table('tbl_categories')
			->fields('alias')
			->where("id=%d", $id)
			->where("status_id IN (1,2)")
			->limit(1);
		
		$category = $query->execute()->row();
		if ($category) {
			if (!empty($category['alias'])) {
				$result = $category['alias'];
			}
		}

		return $result;
	}

	/**
	 * Return full list of categories in array
	 */
	public static function getFullList($type_id = 0, $sort = true) {
		$result = array();

		// Root category
		$result[-1][] = array(
			'id' => 0,
			'parent_id' => -1,
			'name' => __('Root category'),
			'level' => 0,
			'type_id' => 0
		);

		$query = Query::factory('select')
			->table('tbl_categories')
			->where('status_id IN (1,2)');
		
		if ($type_id > 0) {
			$query->where('type_id=%d', $type_id);
		} else {
			$query->orderBy('type_id', Query::ORDER_ASC);
		}

		$query->orderBy('parent_id', Query::ORDER_ASC)
			->orderBy('sequence', Query::ORDER_ASC)
			->orderBy('id', Query::ORDER_ASC);

		$categories = $query->execute()->rows();
		foreach ($categories as $category) {
			$result[(int) $category['parent_id']][(int) $category['id']] = $category;
		}

		if ($sort) {
			$result = self::sortFullList($result);
		}
		
		return $result;
	}

	/**
	 * Return sorted categories list
	 */
	public static function sortFullList($categories, $parent_id = -1, $level = 0) {
		$result = array();
		
		foreach ($categories[$parent_id] as $category) {
			$category['level'] = $level;
			$result[] = $category;
			if (isset($categories[(int)$category['id']])) {
				$result = array_merge($result, self::sortFullList($categories, $category['id'], ($level + 1)));
			}
		}

		return $result;
	}

	/**
	 * Return list of categories
	 */
	public static function getFullPath($id, $type = 'string') {

		if ($id == 0) {
			return __('Root category');
		}

		$cat_info = self::getInfo($id, 'name,parent_id');
		return self::getFullPath(intval($cat_info['parent_id']), $type) . ' / ' . $cat_info['name'];
	}

	/**
	 * Insert category to database
	 */
	public static function insert($data) {
		$query = Query::factory('insert')
			->table('tbl_categories')
			->data($data);
		
		$id = intval($query->execute()->getLastId());
		
		if ($id > 0) {
			self::fixSequence($data['parent_id']['value']);
			Schema::saveFields('category.xml', 'tbl_categories_fields', 'category_id', $id, true);
		}

		return $id;
	}

	/**
	 * Update category
	 */
	public static function update($id, $data) {
		$info = self::getInfo($id, 'parent_id');
		$prev_parent_id = (int) $info['parent_id'];
		
		$query = Query::factory('update')
			->table('tbl_categories')
			->data($data)
			->where("id=%d", $id);

		$result = ($query->execute()->getAffectedRows() > 0);	
		
		if ($result) {
			if ($data['parent_id']['value'] != $prev_parent_id) {
				self::fixSequence($data['parent_id']['value']);
				self::fixSequence($prev_parent_id);
			}
			Schema::saveFields('category.xml', 'tbl_categories_fields', 'category_id', $id);
		}
		
		return $result;
	}

	/**
	 * Delete category
	 */
	public static function delete($id, $data) {
		$info = self::getInfo($id, 'parent_id');
		$parent_id = (int) $info['parent_id'];
		
		$query = Query::factory('update')
			->table('tbl_categories')
			->data($data)
			->where("id=%d", $id);

		$result = ($query->execute()->getAffectedRows() > 0);	
		
		if ($result) {
			self::fixSequence($parent_id);
		}
		
		return $result;
	}

	/**
	 * Move category up in sequence
	 */
	public static function moveUp($id) {
		$error = 0;
		$source = self::getInfo($id, 'id,parent_id,type_id,sequence');
		$destination = Query::factory('select')
			->table('tbl_categories')
			->fields('id,sequence')
			->where("parent_id=%d", $source['parent_id'])
			->where("type_id=%d", $source['type_id'])
			->where("sequence<%d", $source['sequence'])
			->where("status_id IN (1,2)")
			->orderBy('sequence', Query::ORDER_DESC)
			->limit(1)->execute()->row();

		if ($destination) {
			$data = array();
			Query::data($data, 'sequence', $source['sequence'], Query::DATA_INTEGER);
			
			$is_error = Query::factory('update')
				->table('tbl_categories')
				->data($data)
				->where('id=%d', $destination['id'])
				->execute()->isError();
	
			if ($is_error) {
				$error = 1;
			}
			
			if (!$error) {
				$data = array();
				Query::data($data, 'sequence', $destination['sequence'], Query::DATA_INTEGER);

				$is_error = Query::factory('update')
					->table('tbl_categories')
					->data($data)
					->where('id=%d', $source['id'])
					->execute()->isError();

				if ($is_error) {
					$error = 2;
				}
			}
		}
		
		if (!$error && !self::checkSequence($source['parent_id'])) {
			self::fixSequence($source['parent_id']);
		}
		
		return $error;
	}

	/**
	 * Move category down in sequence
	 */
	public static function moveDown($id) {
		$error = 0;
		$source = self::getInfo($id, 'id,parent_id,type_id,sequence');
		$destination = Query::factory('select')
			->table('tbl_categories')
			->fields('id,sequence')
			->where("parent_id=%d", $source['parent_id'])
			->where("type_id=%d", $source['type_id'])
			->where("sequence>%d", $source['sequence'])
			->where("status_id IN (1,2)")
			->orderBy('sequence', Query::ORDER_ASC)
			->limit(1)->execute()->row();

		if ($destination) {
			$data = array();
			Query::data($data, 'sequence', $source['sequence'], Query::DATA_INTEGER);
			
			$is_error = Query::factory('update')
				->table('tbl_categories')
				->data($data)
				->where('id=%d', $destination['id'])
				->execute()->isError();
	
			if ($is_error) {
				$error = 1;
			}

			if (!$error) {
				$data = array();
				Query::data($data, 'sequence', $destination['sequence'], Query::DATA_INTEGER);

				$is_error = Query::factory('update')
					->table('tbl_categories')
					->data($data)
					->where('id=%d', $source['id'])
					->execute()->isError();

				if ($is_error) {
					$error = 2;
				}
			}
		}
		
		if (!$error && !self::checkSequence($source['parent_id'])) {
			self::fixSequence($source['parent_id']);
		}
		
		return $error;
	}

	public static function fixSequence($id = 0) {
		if ($id > 0) {
			$categories = Query::factory('select')
				->table('tbl_categories')
				->fields('id')
				->where("(parent_id=%d) AND (status_id IN (1,2))",  $id)
				->orderBy('sequence', 'ASC')
				->orderBy('id', 'ASC')
				->execute()->rows();

			$sequence = 1;
			foreach ($categories as $category) {
				Query::factory('update')
					->table('tbl_categories')
					->field('sequence', $sequence)
					->where("id=%d", $category['id'])
					->execute();
				$sequence++;
			}
		} else {
			foreach (array(1,2) as $type_id) {
				$categories = Query::factory('select')
					->table('tbl_categories')
					->fields('id')
					->where("(parent_id=%d) AND (type_id=%d) AND (status_id IN (1,2))",  $id, $type_id)
					->orderBy('sequence', 'ASC')
					->orderBy('id', 'ASC')
					->execute()->rows();

				$sequence = 1;
				foreach ($categories as $category) {
					Query::factory('update')
						->table('tbl_categories')
						->field('sequence', $sequence)
						->where("id=%d", $category['id'])
						->execute();
					$sequence++;
				}
			}
		}
	}

	public static function checkSequence($id = 0) {
		$result = true;
		
		$count = Query::factory('select')
			->table('tbl_categories')
			->fields('id')
			->where("(parent_id=%d) AND (status_id IN (1,2)) AND (sequence=9999999)",  $id)
			->limit(1)
			->execute()->count();

		if ($count > 0) {
			$result = false;
		}
		
		return $result;
	}
}
